-------------- liệt kê dách sách đon hàng trong bản chuyên thu ---------------  CREATE 
--ALTER
PROC SP_LietKeThongTinChuyenThu
AS
BEGIN
	SELECT * FROM CHUYENTHU
END   SP_LietKeThongTinChuyenThu  ------------------Cập nhật trong bảng chuyen thu------------------------------------
create PROC [dbo].[SP_CapNhatChuyenThu]
	@ma_dh varchar(6),
	@MaMa varchar(6),
	@NgayThu datetime,
	@MaNV varchar(6),
	@SLHopThu int,
	@SLHopThieu int
		
AS
BEGIN TRY
	--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	begin tran
			update CHUYENTHU 
			set NgayGioTH = @NgayThu,MaNVTH = @MaNV,SLHopThu = @SLHopThu,SlHopThieu = @SLHopThieu 	
			where MaDH = @ma_dh and MaMA = @MaMa
			declare @SetTinhTrang int
			set @SetTinhTrang = (select SLHopThieu  from ChuyenThu where MaDH = @ma_dh and MaMA = @MaMa)
			if(@SetTinhTrang != 0)
				update CHUYENTHU set MaTT = 4 
				where MaDH = @ma_dh and MaMA = @MaMa
			else
				update CHUYENTHU set MaTT = 5 
				where MaDH = @ma_dh and MaMA = @MaMa 
			
		--waitfor delay '00:00:05'
		--select * from CHUYENTHU
	commit tran
END TRY
	BEGIN CATCH
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		rollback tran
		return

	END CATCH
	

exec SP_CapNhatChuyenThu 'DH002','MA032','2013-10-08 12:45:00.000','NV001',0,2	
------------------------------------------------------------------------------------
CREATE
--alter 
PROC [dbo].[SP_LietKeDonHangDangThu]
AS
BEGIN TRY
	
	begin tran
		SET TRAN ISOLATION LEVEL repeatable read	
		--waitfor delay '00:00:10'
		select * 
		from chuyenthu  
		where SlHopThieu != 0
		
	commit tran END TRY
	BEGIN CATCH
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		rollback tran
		return

	END CATCH

----------------------------------------------------------------------------
CREATE
--alter 
PROC [dbo].[SP_LietKeDonHangHoanTat]
AS
BEGIN TRY
	--SET tran ISOLATION LEVEL repeatable read
	begin tran
		declare @count int
		set @count = (select count(MaDH)
						  from chuyenthu
						  where SLHopThieu != 0)
		waitfor delay '00:00:05'
		print N'Số Đơn hàng đang thu' + cast(@count as char(3))
		select * 
		from chuyenthu c 
		where c.SLHopThieu = 0 
				
	commit
END TRY
	BEGIN CATCH
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		rollback tran
		return

	END CATCH	
	
--------------------liệt kê đơn hàng trong khoảng thơi gian--------------------------------

create 
--alter
PROC [dbo].[SP_LietKeDonHangTheoNgay]
@NgayBatDau datetime,
@NgayKetThuc datetime
AS
BEGIN TRY
	begin tran
		select * 
		from chuyenthu 
		where NgayGioTH between @NgayBatDau and @NgayKetThuc   			
	commit
END TRY
	BEGIN CATCH
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		rollback tran
		return
	END CATCH	


------------------Thêm đơn hang trong bảng chuyen thu------------------------------------
CREATE
--alter 
PROC [dbo].[SP_ThemChuyenThu]
	@ma_dh varchar(6),
	@MaMa varchar(6),
	@NgayThu datetime,
	@MaNV varchar(6),
	@SLHopThu int,
	@SLHopThieu int
		
AS
BEGIN TRY
	--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	begin tran
			if(@SLHopThieu != 0)
				INSERT INTO ChuyenThu(MaDH,MaMA,NgayGioTH,MaTT,MaNVTH,SLHopThu,SlHopThieu)
				values (@ma_dh,@MaMa,@NgayThu,4,@MaNV,@SLHopThu,@SLHopThieu)
			else	
				INSERT INTO ChuyenThu(MaDH,MaMA,NgayGioTH,MaTT,MaNVTH,SLHopThu,SlHopThieu)
				values (@ma_dh,@MaMa,@NgayThu,5,@MaNV,@SLHopThu,@SLHopThieu)
			
	commit tran
END TRY
	BEGIN CATCH
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		rollback tran
		return

	END CATCH
	

SP_ThemChuyenThu 'DH003','MA030','2013-10-08 12:45:00.000','NV001',0,2	




